package biz.bbtec.sbm.client.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.struts2.ServletActionContext;

import biz.bbtec.sbm.client.bean.ClientBean;
import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.user.bean.UserBean;

import com.opensymphony.xwork2.ActionContext;

public class ClientService {

	dbConnection db = new dbConnection();

	public List showallclient(int pagenow, int pagesize) {
		List<ClientBean> list = new ArrayList<ClientBean>();
		int now=pagenow*pagesize;
		String sql = "select * from client limit "+now+","+pagesize+"";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql);
			rs = past.executeQuery();
			while (rs.next()) {
				ClientBean client = new ClientBean();
				client.setClientid(rs.getInt("clientid"));
				client.setDescription(rs.getString("description"));
				client.setLocation(rs.getString("location"));
				client.setManager(rs.getString("manager"));
				client.setName(rs.getString("name"));
				client.setPhone(rs.getString("phone"));
				list.add(client);
			}
			rs.close();
			past.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}
	
	public int CountAllClient(){
		int total=0;
		Map session=ActionContext.getContext().getSession();
		UserBean user=(UserBean)session.get("user");
		int userid= user.getUserid();
		String sql1="select count(*)from client ";
		Connection conn=db.getConnection();
		ResultSet rs=null;
		PreparedStatement past=null;
		try{
			past=conn.prepareStatement(sql1);
			rs=past.executeQuery();
			if(rs.next()){
				total=rs.getInt(1);
			}
			rs.close();
			past.close();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			db.closeConnection(conn);
		}
		return total;
	}
	public InputStream printclient() throws Exception{
		HSSFWorkbook wb=new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("sheet1");
		sheet.setColumnWidth(1, 4500);  //设置excel 列宽
		sheet.setColumnWidth(2, 5000);
		sheet.setColumnWidth(3, 3000);
		System.out.println("called printclient");
		//创建单元格样式
		HSSFCellStyle style=wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		
		//设置边框
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		HSSFCellStyle style1=wb.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	
			String sql = "select * from client";
			Connection conn = db.getConnection();
			PreparedStatement past = null;
			ResultSet rs = null;
			int i=1;
			HSSFRow row0= sheet.createRow(0);
			HSSFCell cellA=row0.createCell(0);
			cellA.setCellStyle(style1);
			HSSFCell cellB=row0.createCell(1);
			cellB.setCellStyle(style1);
			HSSFCell cellC=row0.createCell(2);
			cellC.setCellStyle(style1);
			HSSFCell cellD=row0.createCell(3);
			cellD.setCellStyle(style1);
			
			cellA.setCellValue("编号");
			cellB.setCellValue("姓名");
			cellC.setCellValue("地址");
			cellD.setCellValue("电话");
			try {
				past = conn.prepareStatement(sql);
				rs = past.executeQuery();
				while (rs.next()) {
					HSSFRow row= sheet.createRow(i);
					HSSFCell cell0= row.createCell(0);
					cell0.setCellStyle(style);
					cell0.setCellValue(rs.getInt("clientid"));
					HSSFCell cell1= row.createCell(1);
					cell1.setCellStyle(style);
					cell1.setCellValue(rs.getString("name"));
					HSSFCell cell2= row.createCell(2);
					cell2.setCellStyle(style);
					cell2.setCellValue(rs.getString("location"));
					HSSFCell cell3= row.createCell(3);
					cell3.setCellStyle(style);
					cell3.setCellValue(rs.getString("phone"));
					i++;
				}
			}catch (SQLException e) {
					e.printStackTrace();
				} finally {
					rs.close();
					db.closeConnection(conn);
				}	
			String fileName;
			fileName = "联系人.xls";
		//	fileName =  new String(fileName.getBytes(), "ISO8859-1"); 		   
			ByteArrayOutputStream baos= new ByteArrayOutputStream();
			wb.write(baos);
			baos.flush();
			 byte[] aa = baos.toByteArray();
			 InputStream inputStream;
		       inputStream = new ByteArrayInputStream(aa, 0, aa.length);
		        baos.close();
		        String path = ServletActionContext.getServletContext().getRealPath(
						"images");
		        FileOutputStream os = new FileOutputStream(path+"\\contact.xls");
			    wb.write(os);
			    os.close();
		return inputStream;
	}
	public boolean addclient(ClientBean client) throws ParseException,
			SQLException {
		boolean flag = false;
		Map session = ActionContext.getContext().getSession();
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = null;
			String sql1 = "insert into client(name,description,location,phone,manager)values ("
					+ "'"
					+ client.getName()
					+ "'"
					+ ","
					+ "'"
					+ client.getDescription()
					+ "'"
					+ ","
					+ "'"
					+ client.getLocation()
					+ "'"
					+ ","
					+ "'"
					+ client.getPhone()
					+ "'"
					+ ","
					+ "'"
					+ client.getManager()
					+ "'" + ")";
			
			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean deleteclient(ClientBean client) {
		boolean flag = false;
		Connection conn = db.getConnection();
		String sql = "delete from client where clientid='"
				+ client.getClientid() + "'";
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean modifyclient(ClientBean client) {
		boolean flag = false;
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		String sql = "update client set name=?, description=?,location=?,phone=?,manager=? where clientid=?";
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, client.getName());
			past.setString(2, client.getDescription());
			past.setString(3, client.getLocation());
			past.setString(4, client.getPhone());
			past.setString(5, client.getManager());
			past.setInt(6, client.getClientid());
			past.executeUpdate();
			past.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public ClientBean ShowOneClient(ClientBean clientbean)
			throws ParseException {
		ClientBean client = new ClientBean();
		String sql = "select * from client where clientid = ? ";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, clientbean.getClientid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			client.setClientid(Integer.parseInt(rs.getString("clientid")));
			client.setName(rs.getString("name"));
			client.setDescription(rs.getString("description"));
			client.setLocation(rs.getString("location"));
			client.setManager(rs.getString("manager"));
			client.setPhone(rs.getString("phone"));
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return client;
	}
}
